package org.clipjoint.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.lang3.StringUtils;
import org.clipjoint.dao.GameAdminDao;
import org.clipjoint.entity.dto.GameDto;
import org.clipjoint.entity.Game;
import org.clipjoint.util.DataSourceUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 陈光耀
 * @date 2021/2/22
 */
public class GameAdminDaoImpl implements GameAdminDao {


    //查询所有的游戏
    @Override
    public List<Game> selectAll(GameDto gameDto) throws SQLException {

        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 动态sql
        String sql = "select `game`.*,`publisher`.`name` as `publisher_name` from `game`,`publisher` where `game`.`publisher_id` = `publisher`.id";
        List<Object> params = new ArrayList<>();

        // 判断条件查询
        if (StringUtils.isNotBlank(gameDto.getName())) {
            sql += " and `game`.name like ?";
            params.add("%" + gameDto.getName() + "%");
        }

        if (gameDto.getType() != null) {
            sql += " and `game`.type = ?";
            params.add(gameDto.getType().name());
        }
        if (gameDto.getCreateTimeStart() != null && gameDto.getCreateTimeEnd() != null) {
            sql += " and `game`.time >= ? and `game`.time <= ?";
            params.add(gameDto.getCreateTimeStart());
            params.add(gameDto.getCreateTimeEnd());
        }
        if (gameDto.getMinprice() != null && gameDto.getMaxprice() != null) {
            sql += " and `price` between ? and ?";
            params.add(gameDto.getMinprice());
            params.add(gameDto.getMaxprice());
        }

        // 判断排序参数
        if (StringUtils.isNoneBlank(gameDto.getOrderField(), gameDto.getOrderType())) {
            sql += " order by `game`." + gameDto.getOrderField() + " " + gameDto.getOrderType();
        }

        // 分页参数
        if (gameDto.getPage() != null && gameDto.getLimit() != null) {
            sql += " limit ?,?";
            params.add((gameDto.getPage() - 1) * gameDto.getLimit());
            params.add(gameDto.getLimit());
        }

        // 查询所有结果
        List<Game> result = qr.query(sql, new BeanListHandler<>(Game.class), params.toArray());

        return result;
    }

    @Override
    public Long selectCount(GameDto gameDto) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 动态sql
        String sql = "select count(*) from `game` where 1 = 1";
        List<Object> params = new ArrayList<>();

        // 判断条件查询
        if (StringUtils.isNotBlank(gameDto.getName())) {
            sql += " and name like ?";
            params.add("%" + gameDto.getName() + "%");
        }
        if (gameDto.getType() != null) {
            sql += " and type = ?";
            params.add(gameDto.getType().name());
        }
        if (gameDto.getCreateTimeStart() != null && gameDto.getCreateTimeEnd() != null) {
            sql += " and time >= ? and time <= ?";
            params.add(gameDto.getCreateTimeStart());
            params.add(gameDto.getCreateTimeEnd());
        }
        if (gameDto.getMinprice() != null && gameDto.getMaxprice() != null) {
            sql += " and `price` between ? and ?";
            params.add(gameDto.getMinprice());
            params.add(gameDto.getMaxprice());
        }

        // 查询一个结果
        Map<String, Object> result = qr.query(sql, new MapHandler(), params.toArray());

        return (Long) result.get("count(*)");
    }


    //添加一条游戏数据
    @Override
    public int insert(Game game) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "insert into `game`(`name`,`nickname`,`preprice`,`content`,`price`,`type`,`time`,`image`,`off`,`credit`,`system`,`publisher_id`) value(?,?,?,?,?,?,?,?,?,?,?,?)";
        int result = qr.update(sql, game.getName(),game.getNickname(), game.getPreprice(), game.getContent(), game.getPrice(), game.getType().name(), game.getTime(), game.getImage(), game.getOff(), game.getCredit(), game.getSystem(), game.getPublisher_id());
        return result;
    }


    //表头批量删除数据
    @Override
    public int deleteById(Long id) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "delete from `game` where `id` = ?";
        int result = qr.update(sql, id);
        return result;
    }


    //查看每条游戏的详细数据

    @Override
    public Game selectOne(GameDto gameDto) throws SQLException {

        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select `game`.*,`publisher`.`name` as `publisher_name` from `game`,`publisher` where `game`.`publisher_id` = `publisher`.id";
        List<Object> params = new ArrayList<>();

        // 判断条件查询
        if (gameDto.getId() != null) {
            sql += " and `game`.id = ?";
            params.add(gameDto.getId());
        }
        if (StringUtils.isNotBlank(gameDto.getName())) {
            sql += " and name like ?";
            params.add("%" + gameDto.getName() + "%");
        }
        if (gameDto.getType() != null) {
            sql += " and type = ?";
            params.add(gameDto.getType().name());
        }
        if (gameDto.getCreateTimeStart() != null && gameDto.getCreateTimeEnd() != null) {
            sql += " and time >= ? and time <= ?";
            params.add(gameDto.getCreateTimeStart());
            params.add(gameDto.getCreateTimeEnd());
        }
        if (gameDto.getMinprice() != null && gameDto.getMaxprice() != null) {
            sql += " and `price` between ? and ?";
            params.add(gameDto.getMinprice());
            params.add(gameDto.getMaxprice());
        }

        // 查询一个结果
        Game result = qr.query(sql, new BeanHandler<>(Game.class), params.toArray());

        return result;
    }


    //修改一条数据
    @Override
    public int update(Game game) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "update `game` set `name` = ?, `nickname` = ?, `preprice` = ?, `content` = ?, `type` = ?, `image` = ?, `off` = ?, `price` = ?, `credit` = ?, `system` = ? where `id` = ?";

        int result = qr.update(sql, game.getName(),game.getNickname(), game.getPreprice(), game.getContent(), game.getType().name(), game.getImage(), game.getOff(), game.getPrice(), game.getCredit(), game.getSystem(), game.getId());

        return result;
    }

}
